package database.dbRelations;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import objects.File;
import objects.Task;

import main.Main;
import database.dbObjects.IDBObject;
import database.dbObjects.IDBFile.DBFile;
import database.dbObjects.IDBTask.DBTask;

public interface IDBRelTaskFile extends IDBObject {
	
	public class DBRelTaskFile extends DBObject {
		
		public final void init() throws Exception {
			
			createTable();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table RelTaskFile( " +
	        			   "Task_Name varchar(" + DBTask.maxLengthName + ") not null, " +
	        			   "File_Pathname varchar(" + DBFile.maxLengthPath + ") not null, " +
	        			   "foreign key (Task_Name) references Task (Name), " +
	        			   "foreign key (File_Pathname) references File (Pathname), " +
	        			   "primary key (Task_Name, File_Pathname) " +
	        			       "on conflict abort);";
			
			executeCreation(query);
		}
		
		// TODO eventual triggers
		
		public final boolean add(String taskName, String filePath) throws Exception {
			
			checkConsistance(taskName, filePath);
			
			Vector<Object> v = new Vector<Object>(2);
			v.add(0, taskName);
			v.add(1, filePath);
			
			return super.add("RelTaskFile", v);
		}
		
		public final boolean delete(String taskName, String filePath) throws Exception {
            
            checkConsistance(taskName, filePath);
            
            Vector<Object> keys = new Vector<Object>(2);
            keys.add(0, taskName);
            keys.add(1, filePath);
            
            return super.delete("RelTaskFile", keyColumns, keys);
		}
		
	/*	public void printRelTaskFile(String sql) {
			// TODO nog aan te passen!!
			try {
				Statement stat = Main.connection.createStatement();
				ResultSet rs = stat.executeQuery(sql);
				
				System.out.println("Vak ID\t\t|\tFile naam");
				System.out.println("---------------------------------------------------------------");
				while (rs.next()) {
					System.out.println(rs.getString("Course_ID")+"\t\t|\t"+rs.getString("File_Path"));
				}
				rs.close();
			} catch(Exception e){
				System.out.println("test-Exception P10!!!");
			}	
		}
	*/	
		
		public final Vector<Task> getTasks(String filePath) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelTaskFile join Task " + 
					"where ((File_Pathname = '" + filePath + "') and " + 
					"(RelTaskFile.Task_Name = Task.Name));");
			
			Vector<Task> tasks = new Vector<Task>();
			int counter = 0;
			while (rs.next()) {
				tasks.add(counter, new Task(rs.getString("Name"), 
						rs.getString("Description"), rs.getBoolean("Done"), 
						rs.getInt("Grades"), rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return tasks;
		}
		
		public final Vector<File> getFiles(String taskName) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelTaskFile join File " + 
					"where ((Task_Name = '" + taskName + "') and " + 
					"(RelTaskFile.File_Pathname = File.Pathname));");
			
			Vector<File> files = new Vector<File>();
			int counter = 0;
			while (rs.next()) {
				files.add(counter, new File(rs.getString("Pathname"), 
						rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return files;
		}
		
		private final void checkConsistance(String taskName, String filePath) throws Exception {
			
			task.checkName(taskName);
			file.checkPathname(filePath);
		}
		
		
		private static final Vector<String> keyColumns = new Vector<String>(2);
		static {
			keyColumns.add(0, "Task_Name");
			keyColumns.add(1, "File_Pathname");
		}
		private final DBTask task = new DBTask();
		private final DBFile file = new DBFile();
	
	}
	
}
